set define off;
set scan off;


create or replace directory loanDirectory as 'D:\Kiva\kiva_ds_xml\loans';
create or replace directory lenderDirectory as 'D:\Kiva\kiva_ds_xml\lenders';


--TODO: Use smaller blocks of XML instead of parsing the entire file in XPath each time.  See the way lender() is done.

create or replace package pkg_dataLoad as
  --Main functions.  To refresh data, run them in this order:
  procedure removeData;

  procedure loadPartner;
  procedure loadLoan;
  procedure loadLender;
  procedure loadLoanLender;

  procedure fixValues;


  --Helper functions:
  function extractAndGetStringVal(xml in xmltype, xpath in varchar2) return varchar2;
  function extractAndGetClobVal(xml in xmltype, xpath in varchar2) return clob;
  function getXMLFromURL(url in varchar2) return xmltype;
  function convertBlobToXML(xmlBlob in blob) return xmltype;

  --Old methods:
  --procedure logException(programName in varchar2, customData in varchar2, error_stack in varchar2
  --  ,error_backtrace in varchar2, moreDataClob in clob default null, moreDataBlob in blob default null);
  --procedure loadLenderSearch(maximumNumberOfPagesToLoad in number default null);
  --procedure loadLender;
end pkg_dataLoad;
/




create or replace package body pkg_dataLoad as

------------------------------------------------------------------------------------------------------------------------
--Purpose: Truncate most of the data, such as lender, loan, and partner information.
--         This does not affect loan_lender, country,  or region.
--         This procedure should only take a few seconds
------------------------------------------------------------------------------------------------------------------------
procedure removeData is
begin

  --Remove data from lender tables
  --Loan_Lender is the only table that can be incrementally updated
  --execute immediate 'truncate table loan_lender';

  execute immediate 'truncate table lender';


  --Remove data loan tables  
  execute immediate 'truncate table borrower';
  execute immediate 'truncate table loanDescription';
  execute immediate 'truncate table loansAvailableWhenPosted';
  execute immediate 'truncate table loan_language';
  execute immediate 'truncate table localPayment';
  execute immediate 'truncate table payment';
  execute immediate 'truncate table scheduledPayment';


  --The LOAN table is refereneced by foreign keys.
  --1. Disable the foreign keys
  for loan_constraints in
  (
    select constraint_name, table_name
    from user_constraints
    where status = 'ENABLED'
      and r_constraint_name in
      (
        select constraint_name from user_constraints where table_name = 'LOAN'
      )
  ) loop
    execute immediate 'alter table '||loan_constraints.table_name||' disable constraint '
      ||loan_constraints.constraint_name;
  end loop;
  
  --2. Truncate the data
  execute immediate 'truncate table loan';
  
  --3. Re-enable foreign keys
  for loan_constraints in
  (
    select constraint_name, table_name
    from user_constraints
    where status = 'DISABLED'
      and r_constraint_name in
      (
        select constraint_name from user_constraints where table_name = 'LOAN'
      )
  ) loop
    execute immediate 'alter table '||loan_constraints.table_name||' enable constraint '
      ||loan_constraints.constraint_name;
  end loop;


  --Partner tables
  delete from partner_country;
  delete from partner;
  --delete from country;
  --delete from region;
  
  --Other
  execute immediate 'truncate table exceptions';
  commit;  
end;

------------------------------------------------------------------------------------------------------------------------
--Purpose: Execute the XPath expression on the XML.
------------------------------------------------------------------------------------------------------------------------
function extractAndGetStringVal(xml in xmltype, xpath in varchar2) return varchar2 is
  tempXML xmltype;
begin
    tempXML := xml.extract(xpath);
    if tempXML is null then
      return null;
    else
      return tempXML.getStringVal();
    end if;
end extractAndGetStringVal;


------------------------------------------------------------------------------------------------------------------------
--Purpose: Execute the XPath expression on the XML.
------------------------------------------------------------------------------------------------------------------------
function extractAndGetClobVal(xml in xmltype, xpath in varchar2) return clob is
  tempXML xmltype;
begin
    tempXML := xml.extract(xpath);
    if tempXML is null then
      return null;
    else
      return tempXML.getClobVal();
    end if;
end extractAndGetClobVal;



------------------------------------------------------------------------------------------------------------------------
--Purpose: Search a clob for a string and replace it with spaces.  Clobs can't truly replace.
--Inputs: v_clob - clob that will be altered, v_replace - string to search for, cannot be null
------------------------------------------------------------------------------------------------------------------------
procedure clobReplaceWithSpaces(v_clob in out clob, v_replace in varchar2) is
  position number;
begin
  --Don't allow nulls
  if v_replace is null then
    raise_application_error(-20001, 'String to replace cannot be null.');
  end if;

  --Keep track of the position so we don't have to re-search the entire CLOB.
  position := 1;
  loop
    --Look for pattern
    position := dbms_lob.instr(v_clob, v_replace, position);
    --If not found, then exit
    if position = 0 then
      exit;
    --If found, write over the string.  CLOBs can't simply replace, we can only overwrite the values.
    else
      dbms_lob.write(v_clob, length(v_replace), position, lpad(' ', length(v_replace), ' '));
    end if;
  end loop;
end;


------------------------------------------------------------------------------------------------------------------------
--Purpose: Convert strings in the ISO 8601 format into Oracle dates.
--Inputs: iso8601Date - A string in the format 2005-04-14T05:27:55Z
--Returns: An Oracle date.
------------------------------------------------------------------------------------------------------------------------
function iso8601ToDate(iso8601Date in varchar2) return date is
begin
  return to_date(replace(replace(iso8601Date, 'T', ' '), 'Z', null), 'YYYY-MM-DD HH24:MI:SS');
end iso8601ToDate;



------------------------------------------------------------------------------------------------------------------------
--Purpose: Convert a BLOB to an XMLTYPE.  Although there are some very simple functions to do this there are several
--  common problems with the Kiva data and this function can help avoid those issues.
--Inputs: obvious
------------------------------------------------------------------------------------------------------------------------
function convertBlobToXML(xmlBlob in blob) return xmltype is
  xmlClob clob;
  destinationOffset number := 1;
  sourceOffset number := 1;
  blobCSID number := dbms_lob.default_csid;
  languageContext integer := dbms_lob.default_lang_ctx;
  warning integer;

  xmlParsingFailed exception;
  pragma exception_init(xmlParsingFailed, -31011);
begin
  --Although there are more direct ways to convert to XMLType, such as httpuri.getxml() and httpuir.getclob(), each of
  --those functions occasionally throws Oracle 600 errors.  I think this has something to do with non-ascii characters
  --after the 8 thousandth character.  The only safe way to get the data is to first get a blob, convert to a clob, 
  --and then convert to xml. -(Note: A 600 error can be caught the session will still terminate, so there's no point.)

  --TODO: Is this character conversion correct?  The default worked during my testing, but I don't understand why.
  dbms_lob.createtemporary(xmlClob, true);

  destinationOffset := 1; sourceOffset := 1; languageContext := dbms_lob.default_lang_ctx;
  dbms_lob.convertToClob(xmlClob, xmlBlob, dbms_lob.lobmaxsize, destinationOffset, sourceOffset, 
    blobCSID, languageContext, warning);

  --Try to convert the clob to an XMLTYPE and return it.
  begin
    return xmltype(xmlClob);
  exception when xmlParsingFailed then
    --If it doesn't parse there's a good chance there are some invalid characters.  Replace them with spaces.
    --These are invalid XML characters I've found in the past:
    clobReplaceWithSpaces(xmlClob, '&#26;');
    clobReplaceWithSpaces(xmlClob, '&#16;');
    return xmltype(xmlClob);
  end;

  /*
  exception when others then
    logException('Lender Load', 'Url = '||url, dbms_utility.format_error_stack
      ,dbms_utility.format_error_backtrace, null, null);
    raise;
  */
end;


------------------------------------------------------------------------------------------------------------------------
--Purpose: Download the contents of a URL and put it in an XMLTYPE.
--Inputs: obvious
------------------------------------------------------------------------------------------------------------------------
function getXMLFromURL(url in varchar2) return xmltype is
  httpuri HttpUriType;
  xmlBlob blob;
begin
  --Load URL
  httpuri := HttpUriType(url);

  --Get BLOB
  dbms_lob.createtemporary(xmlBlob, true);
  xmlblob := httpuri.getblob();

  --Convert Blob into XMLType and return it
  return convertBlobToXML(xmlblob);
end;







------------------------------------------------------------------------------------------------------------------------
--Purpose: Re-load all partners.  Will also populate REGION, COUNTRY, and PARTNER_COUNTRY
--Requirements: PARTNER, REGION, COUNTYR, and PARTNER_COUNTRY are empty.
--Assumptions: Country information that is listed multiple times is identical each time and is accurate.
--TODO: Test with multiple pages of partners
------------------------------------------------------------------------------------------------------------------------
procedure loadPartner is
  partners xmltype;
  numberOfPages number;
  partnerIndex number;
  v_partner partner%rowtype;
  countryIndex number;
  v_country country%rowtype;
  pairs varchar2(100);
begin
  --Load the first partner page
  partners := getXMLFromURL('http://api.kivaws.org/v1/partners.xml&page=1&app_id=org.kivadata');

  --get the number of pages
  numberOfPages := to_number(extractAndGetStringVal(partners, '/response/paging/pages/text()'));
  
  --Loop through all pages
  for pageIndex in 1 .. numberOfPages loop
    --Load the new partner page (don't reload the first page)
    if pageIndex > 1 then
      partners := getXMLFromURL('http://api.kivaws.org/v1/partners.xml&page='||to_char(numberOfPages)||'&app_id=org.kivadata');
    end if;

    --Reset the partner index
    partnerIndex := 0;

    --Loop through all partners
    loop
      --Get the partner_id to see if this partner exists
      partnerIndex := partnerIndex + 1;
      v_partner.id := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/id/text()');

      --Stop looping when we hit the first null partner_id
      exit when v_partner.id is null;

      --Load all of the fields
      v_partner.name := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/name/text()');
      v_partner.status := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/status/text()');
      v_partner.rating := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/rating/text()');
      v_partner.image_id := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/image/id/text()');
      v_partner.template_id := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/image/template_id/text()');
      v_partner.start_date := iso8601ToDate(extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/start_date/text()'));
      v_partner.delinquency_rate := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/delinquency_rate/text()');
      v_partner.default_rate := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/default_rate/text()');
      v_partner.total_amount_raised := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/total_amount_raised/text()');
      v_partner.loans_posted := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/loans_posted/text()');

      --Insert the partner record
      insert into partner values v_partner;
      
      --Reset the country index
      countryIndex := 0;

      --Loop through all the related countries
      loop
        --v_country := v_country();

        --Get the country_name to see if the country exists
        countryIndex := countryIndex + 1;
        v_country.name := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/countries/country['||countryIndex||']/name/text()');
        --Remove some HTML characters.  For example, Cote D&apos;Ivoire should be Cote D'Ivoire.
        --Also, some countries are not official countries.  For example, South Sudan is converted to Sudan
        v_country.name := replace(replace(v_country.name, '&apos;', ''''), 'South Sudan', 'Sudan');

        --Stop looping when we hit the first null country name
        exit when v_country.name is null;
  
        --Load all of the fields
        v_country.iso_code := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/countries/country['||countryIndex||']/iso_code/text()');
        v_country.region := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/countries/country['||countryIndex||']/region/text()');
        --Pairs are stored as "latitude longitude".  So get numbers before and after first space.
        pairs := extractAndGetStringVal(partners, '/response/partners/partner['||partnerIndex||']/countries/country['||countryIndex||']/location/geo/pairs/text()');
        v_country.latitude := substr(pairs, 1, instr(pairs, ' ')-1 );
        v_country.longitude := substr(pairs, instr(pairs, ' ')+1 );

        --Add row to region if necessary
        insert into region
        (select v_country.region, null, null
         from dual
         where v_country.region not in (select name from region));

        --TEST
        --dbms_output.put_line('country.name = '||v_country.name||', iso_code = '||v_country.iso_code);

        --Add the row to country if necessary
        insert into country (name, iso_code, region, latitude, longitude)
        (select v_country.name, v_country.iso_code, v_country.region, v_country.latitude, v_country.longitude
         from dual
         where v_country.iso_code not in (select iso_code from country));

        --Add the row to partner_country
        insert into partner_country values (v_partner.id, v_country.name);
      end loop; --each country
    end loop; --each partner
  end loop; --each page

  --Commit any remaining rows
  commit;
end loadPartner;















------------------------------------------------------------------------------------------------------------------------
--Purpose: Re-load all loans and all associated tables.
--Requirements: Many tables that are based loans.
--Assumptions: Loan files are consecutively numbered (the procedure exits on the first missing file).
--Side-Affects: Commits data.
------------------------------------------------------------------------------------------------------------------------
procedure loadLoan is
  fileName varchar2(100);
  loanFile bfile;
  loanXML xmltype;
  localXML xmltype;

  characterSetID number := dbms_lob.default_csid;

  fileNotFound exception;
  pragma exception_init(fileNotFound, -22288);

  loanIndex number;
  v_loan loan%rowtype;

  loanDescriptionIndex number;
  v_loanDescription loanDescription%rowtype;
  
  languageIndex number;
  languageCode varchar2(2);
  
  borrowerIndex number;
  v_borrower borrower%rowtype;

  localPaymentIndex number;
  v_localPayment localPayment%rowtype;

  scheduledPaymentIndex number;
  v_scheduledPayment scheduledPayment%rowtype;

  paymentIndex number;
  v_payment payment%rowtype;
begin

  --Load all of the files.  There's no way in PL/SQL to read the available filenames, so we have to guess how many
  --files will exist.  To be safe, pick a very large number.
  for loanFileID in 1 .. 1000 loop
    fileName := to_char(loanFileID)||'.xml';

    --TEST
    dbms_output.put_line('Loan file = '||fileName);

    loanFile := bfilename('LOANDIRECTORY', fileName);

    --Try to load the file.  Ignore all cannot-find-file errors.  Return if file not found.
    begin
      loanXML := xmltype.createXML(loanFile, characterSetID, null);
    exception when fileNotFound then
      dbms_output.put_line('Could not find file '||fileName||', exiting.');
      return;
    end;

    --Reset loanIndex
    loanIndex := 0;

    --Loop through all loans
    loop
      --Look for the loan id to see if this loan exists.  Exit when there are no more loans.
      loanIndex := loanIndex + 1;

      --Get a smaller block to XML to improve parsing time.
      localXML := loanXML.extract('/snapshot/loans/loan['||loanIndex||']');

      --Stop looping if there are no more lenders
      exit when localXML is null;

      --Stop looping when we hit the first null partner_id
      --exit when v_loan.id is null;

      --TEST (too much output might throw a buffer error)
      --dbms_output.put_line('v_loan.id = '||v_loan.id);

      --Get loan data
      v_loan.id := extractAndGetStringVal(localXML, '/loan/id/text()');
      v_loan.name := extractAndGetStringVal(localXML, '/loan/name/text()');
      v_loan.status := extractAndGetStringVal(localXML, '/loan/status/text()');
      v_loan.funded_amount := extractAndGetStringVal(localXML, '/loan/funded_amount/text()');
      v_loan.paid_amount := extractAndGetStringVal(localXML, '/loan/paid_amount/text()');
      v_loan.image_id := extractAndGetStringVal(localXML, '/loan/image/id/text()');
      v_loan.template_id := extractAndGetStringVal(localXML, '/loan/image/template_id/text()');
      v_loan.activity := extractAndGetStringVal(localXML, '/loan/activity/text()');
      v_loan.sector := extractAndGetStringVal(localXML, '/loan/sector/text()');
      v_loan.use := extractAndGetStringVal(localXML, '/loan/use/text()');
      v_loan.country := extractAndGetStringVal(localXML, '/loan/location/country/text()');
      v_loan.town := extractAndGetStringVal(localXML, '/loan/location/town/text()');
      v_loan.geoLevel := extractAndGetStringVal(localXML, '/loan/location/geo/level/text()');
      v_loan.geoPairs := extractAndGetStringVal(localXML, '/loan/location/geo/pairs/text()');
      v_loan.geoType := extractAndGetStringVal(localXML, '/loan/location/geo/type/text()');
      v_loan.partner_id := extractAndGetStringVal(localXML, '/loan/partner_id/text()');
      v_loan.disbursal_amount := extractAndGetStringVal(localXML, '/loan/terms/disbursal_amount/text()');
      v_loan.disbursal_currency := extractAndGetStringVal(localXML, '/loan/terms/disbursal_currency/text()');
      v_loan.disbursal_date := iso8601ToDate(extractAndGetStringVal(localXML, '/loan/terms/disbursal_date/text()'));
      v_loan.loan_amount := extractAndGetStringVal(localXML, '/loan/terms/loan_amount/text()');
      v_loan.nonpayment := extractAndGetStringVal(localXML, '/loan/terms/loss_liability/nonpayment/text()');
      v_loan.currency_exchange := extractAndGetStringVal(localXML, '/loan/terms/loss_liability/currency_exchange/text()');
      v_loan.posted_date := iso8601ToDate(extractAndGetStringVal(localXML, '/loan/posted_date/text()'));
      v_loan.funded_date := iso8601ToDate(extractAndGetStringVal(localXML, '/loan/funded_date/text()'));
      v_loan.defaulted_date := iso8601ToDate(extractAndGetStringVal(localXML, '/loan/defaulted_date/text()'));
      v_loan.paid_date := iso8601ToDate(extractAndGetStringVal(localXML, '/loan/paid_date/text()'));
      v_loan.refunded_date := iso8601ToDate(extractAndGetStringVal(localXML, '/loan/refunded_date/text()'));
      v_loan.journal_entries := extractAndGetStringVal(localXML, '/loan/journal_totals/entries/text()');
      v_loan.journal_bulk_entries := extractAndGetStringVal(localXML, '/loan/journal_totals/bulk_entries/text()');

      --Insert the loan
      begin
        insert into loan values v_loan;
      --Some of the loans have a duplicate image_id.  The image_id doesn't really matter, but only one of the loans
      --should be loaded, so skip the rest of the loan processing if a unique constraint violation occurs.
      --11g has a continue statement, but for now I'm stuck with either a huge if statement or an evil goto.
      exception when dup_val_on_index then
        dbms_output.put_line('Unique constraint error for loan #'||v_loan.id||' in file '||fileName||
          '.  The loan was probably added, but is it correct?');
        goto end_loan_loop;  --GOTO considered awesome
      end;

      -----------------
      --LOANDESCRIPTION
      -----------------
      --Reset
      loanDescriptionIndex := 0;
      --Loop through all rows 
      loop
        --Increment index, get the code
        loanDescriptionIndex := loanDescriptionIndex + 1;
        --Get the language code to see if there is a description.  This is a little different than the normal extract
        --because we don't know what the element names are.  There is an XPath function for finding the element name, 
        --but that function is not supported by Oracle's XPath.  Since the XML is always something like <en>text..</en>,
        --this code gets that whole string and only reads characters 2 and 3.  The string can be more than 4,000
        --characters, so use CLOB instead of VARCHAR.
        --ASSUMPTION: Language codes are always 2 digits
        v_loanDescription.language_code := dbms_lob.substr(extractAndGetClobVal(localXML, '/loan/description/texts/*['||loanDescriptionIndex||']'), 2, 2);
        --Exit when there are no more languages
        exit when v_loanDescription.language_code is null;
        --Get other values
        v_loanDescription.loan_id := v_loan.id;
        v_loanDescription.text := extractAndGetClobVal(localXML, '/loan/description/texts/*['||loanDescriptionIndex||']/text()');
        --insert
        insert into loanDescription values v_loanDescription;
      end loop;

      ---------------
      --LOAN_LANGUAGE
      ---------------
      --Reset languageIndex
      languageIndex := 0;
      --Loop through all languages 
      loop
        --Increment index, get the code
        languageIndex := languageIndex + 1;
        languageCode := extractAndGetStringVal(localXML, '/loan/description/languages/language['||languageIndex||']/text()');
        --Exit when there are no more languages
        exit when languageCode is null;
        --insert
        insert into loan_language values (v_loan.id, languageCode);
      end loop;

      ----------
      --BORROWER
      ----------
      --Reset
      borrowerIndex := 0;
      --Loop through all rows
      loop
        --Increment index, get the code
        borrowerIndex := borrowerIndex + 1;
        v_borrower.first_name := extractAndGetStringVal(localXML, '/loan/borrowers/borrower['||borrowerIndex||']/first_name/text()');
        v_borrower.last_name := extractAndGetStringVal(localXML, '/loan/borrowers/borrower['||borrowerIndex||']/last_name/text()');
        --Exit when there are no more values.  Some borrowers do not have first names, some don't have last name, but they must have one.
        exit when v_borrower.first_name is null and v_borrower.last_name is null;
        --Get borrower fields
        v_borrower.gender := extractAndGetStringVal(localXML, '/loan/borrowers/borrower['||borrowerIndex||']/gender/text()');
        v_borrower.pictured := extractAndGetStringVal(localXML, '/loan/borrowers/borrower['||borrowerIndex||']/pictured/text()');
        v_borrower.loan_id := v_loan.id;
        --insert
        insert into borrower values v_borrower;
      end loop;

      --------------
      --LOCALPAYMENT
      --------------
      --Reset
      localPaymentIndex := 0;
      --Loop through all rows
      loop
        --Increment index, get values
        localPaymentIndex := localPaymentIndex + 1;
        v_localPayment.amount := extractAndGetStringVal(localXML, '/loan/terms/local_payments/local_payment['||localPaymentIndex||']/amount/text()');
        v_localPayment.due_date := iso8601ToDate(extractAndGetStringVal(localXML, '/loan/terms/local_payments/local_payment['||localPaymentIndex||']/due_date/text()'));
        v_localPayment.loan_id := v_loan.id;
        --Exit when there are no more values.  (I'm not sure which fields are non-null)
        exit when v_localPayment.amount is null and v_localPayment.due_date is null;
        --insert
        insert into localPayment values v_localPayment;
      end loop;

      ------------------
      --SCHEDULEDPAYMENT
      ------------------
      --Reset
      scheduledPaymentIndex := 0;
      --Loop through all rows
      loop
        --Increment index, get values
        scheduledPaymentIndex := scheduledPaymentIndex + 1;
        v_scheduledPayment.amount := extractAndGetStringVal(localXML, '/loan/terms/scheduled_payments/scheduled_payment['||scheduledPaymentIndex||']/amount/text()');
        v_scheduledPayment.due_date := iso8601ToDate(extractAndGetStringVal(localXML, '/loan/terms/scheduled_payments/scheduled_payment['||scheduledPaymentIndex||']/due_date/text()'));
        v_scheduledPayment.loan_id := v_loan.id;
        --Exit when there are no more values.  (I'm not sure which fields are non-null)
        exit when v_scheduledPayment.amount is null and v_scheduledPayment.due_date is null;
        --insert
        insert into scheduledPayment values v_scheduledPayment;
      end loop;

      ---------
      --PAYMENT
      ---------
      --Reset
      paymentIndex := 0;
      --Loop through all rows
      loop
        --Increment index, get first value
        paymentIndex := paymentIndex + 1;
        v_payment.amount := extractAndGetStringVal(localXML, '/loan/payments/payment['||paymentIndex||']/amount/text()');

        --Exit when there are no more values.
        exit when v_payment.amount is null;

        --get other values
        v_payment.payment_id := extractAndGetStringVal(localXML, '/loan/payments/payment['||paymentIndex||']/payment_id/text()');
        v_payment.local_amount := extractAndGetStringVal(localXML, '/loan/payments/payment['||paymentIndex||']/local_amount/text()');
        v_payment.processed_date := iso8601ToDate(extractAndGetStringVal(localXML, '/loan/payments/payment['||paymentIndex||']/processed_date/text()'));
        v_payment.settlement_date := iso8601ToDate(extractAndGetStringVal(localXML, '/loan/payments/payment['||paymentIndex||']/settlement_date/text()'));
        v_payment.commentText := extractAndGetClobVal(localXML, '/loan/payments/payment['||paymentIndex||']/comment/text()');
        v_payment.rounded_local_amount := extractAndGetStringVal(localXML, '/loan/payments/payment['||paymentIndex||']/rounded_local_amount/text()');
        v_payment.loan_id := v_loan.id;

        --insert
        insert into payment values v_payment;
      end loop;

      <<end_loan_loop>>
      null;

    end loop; --end loans

    --Commit after an entire file is loaded
    commit;

  end loop; --end loading loan file

  exception when others then
    dbms_output.put_line('ERROR.  File name = '||fileName||'.  Loan ID = '||v_loan.id||chr(10)||
      dbms_utility.format_error_stack||chr(10)||dbms_utility.format_error_backtrace);
    raise;
end loadLoan;


























------------------------------------------------------------------------------------------------------------------------
--Purpose: Re-load all lenders.
--Requirements: Lender table.
--Assumptions: Lender files are consecutively numbered (the procedure exits on the first missing file).
--Side-Affects: Commits data.
------------------------------------------------------------------------------------------------------------------------
procedure loadLender is
  fileName varchar2(100);
  lenderFile bfile;
  lenderBlob blob;
  lenderXML xmltype;
  localXML xmltype;
  destinationOffset number := 1;
  sourceOffset number := 1;

  fileNotFound exception;
  pragma exception_init(fileNotFound, -22288);

  lenderIndex number;
  v_lender lender%rowtype;
begin
  --Load all of the files.  There's no way in PL/SQL to read the available filenames, so we have to guess how many
  --files will exist.  To be safe, pick a very large number.
  for lenderFileID in 1 .. 10000 loop
    fileName := to_char(lenderFileID)||'.xml';

    --TEST
    --dbms_output.put_line('Lender file = '||fileName);

    --Try to load the file.  Ignore all cannot-find-file errors.  Return if file not found.
    begin
      --Get BFILE
      lenderFile := bfilename('LENDERDIRECTORY', fileName);
      dbms_lob.open(lenderFile, dbms_lob.lob_readonly);

      --Convert BFILE to BLOB (Lender files have invalid characters, must clean BLOB first)
      dbms_lob.createtemporary(lenderBlob, true);
      destinationOffset := 1; sourceOffset := 1; --reset offsets
      dbms_lob.loadBlobFromFile(lenderBlob, lenderFile, dbms_lob.lobMaxSize, destinationOffset, sourceOffset);
      dbms_lob.close(lenderFile);
      lenderXML := convertBlobToXML(lenderBlob);
    exception when fileNotFound then
      dbms_lob.close(lenderFile);
      dbms_output.put_line('Could not find file '||fileName||', exiting.');
      return;
    end;

    --Reset loanIndex
    lenderIndex := 0;

    --Loop through all loans
    loop
      --Look for the lender_id to see if this lender exists.  Exit when there are no more lenders.
      lenderIndex := lenderIndex + 1;

      --Get a smaller block to XML to improve parsing time.
      localXML := lenderXML.extract('/snapshot/lenders/lender['||lenderIndex||']');

      --Stop looping if there are no more lenders
      exit when localXML is null;

      --Get lender data
      v_lender.lender_id := extractAndGetStringVal(localXML, '/lender/lender_id/text()');
      v_lender.name := extractAndGetStringVal(localXML, '/lender/name/text()');
      v_lender.image_id := extractAndGetStringVal(localXML, '/lender/image/id/text()');
      v_lender.template_id := extractAndGetStringVal(localXML, '/lender/image/template_id/text()');
      v_lender.whereabouts := extractAndGetStringVal(localXML, '/lender/whereabouts/text()');
      v_lender.country_code := extractAndGetStringVal(localXML, '/lender/country_code/text()');
      v_lender.lender_uid := extractAndGetStringVal(localXML, '/lender/uid/text()');
      v_lender.member_since := iso8601ToDate(extractAndGetStringVal(localXML, '/lender/member_since/text()'));
      v_lender.personal_url := extractAndGetStringVal(localXML, '/lender/personal_url/text()');
      v_lender.occupation := extractAndGetStringVal(localXML, '/lender/occupation/text()');
      v_lender.loan_because := extractAndGetClobVal(localXML, '/lender/loan_because/text()');
      v_lender.occupational_info := extractAndGetClobVal(localXML, '/lender/occupational_info/text()');
      v_lender.loan_count := extractAndGetStringVal(localXML, '/lender/loan_count/text()');
      v_lender.invitee_count := extractAndGetStringVal(localXML, '/lender/invitee_count/text()');

      --BUG IN BULK DATA.  Very rarely the lender_id can be null, but I assume this should not happen.
      --If the lender_id is null, don't insert the record, it will just fail.
      if v_lender.lender_id is null then
        dbms_output.put_line('Null lender_id in '||fileName||'.  Lender not loaded.  Name = '||v_lender.name);
      else
        --Insert the lender
        insert into lender values v_lender;
      end if;

      <<end_loan_loop>>
      null;
    end loop; --end lenders

    --Commit after an entire file is loaded
    commit;

  end loop; --end loading lender file

  exception when others then
    dbms_output.put_line('ERROR.  File name = '||fileName||'.  Lender ID = '||v_lender.lender_id||chr(10)||
      dbms_utility.format_error_stack||chr(10)||dbms_utility.format_error_backtrace);
    raise;


end loadLender;









----------------------------------------------------------------------------------------------------------------------
--Purpose: Download and insert into loan_lender as necessary.  Exclude loans in fundraising.  Note that some loans
--  do not have any lenders, for example loan.id 100.  This shouldn't be a problem, but it means that the procedure
--  will always try to load the same empty page.
--  This procedure can be used incrementally.
--WARNING: Although it is intended to be used incrementally, it is possible for a lender to change their account from
--  public to private, and thus the old data will not match the new data on Kiva.
--
--Thanks to Brian Duncan for finding several bugs.
----------------------------------------------------------------------------------------------------------------------
procedure loadLoanLender is
  url varchar2(100);
  loan_lenders xmltype;
  numberOfPages number;
  lenderIndex number;
  lender_id varchar2(100);
  name varchar2(999);
  whereabouts varchar2(999);
  lenderOrder number;
begin
  --Loop through all loans not in fundraising that don't exist in loan_lender
  --(Exclude fundraising so that we only load loan_lenders that have a complete list of lenders)
  for loans in (select id from loan where status <> 'fundraising' and id not in (select loan_id from loan_lender)
    order by id)
  loop
    --Load the first page to get the number of pages
    url := 'http://api.kivaws.org/v1/loans/'||to_char(loans.id)||'/lenders.xml&page=1&app_id=org.kivadata';
    loan_lenders := getXMLFromURL(url);
    numberOfPages := to_number(extractAndGetStringVal(loan_lenders, '/response/paging/pages/text()'));
    --pages can be null, although I'm not sure why this would happen.
    if numberOfPages is null then numberOfPages := 0; end if;
    --Reset lender order for each loan
    lenderOrder := 0;

    --Loop through all the pages
    for pageIndex in 1 .. numberOfPages loop
      --Load new page, but don't reload page 1
      if pageIndex > 1 then
        url := 'http://api.kivaws.org/v1/loans/'||to_char(loans.id)||'/lenders.xml&page='||to_char(pageIndex)||'&app_id=org.kivadata';
        loan_lenders := getXMLFromURL(url);
      end if;

      --Loop through each lender on the page
      lenderIndex := 0;
      loop
        --Get the lender_id
        lenderIndex := lenderIndex + 1;
        lender_id := extractAndGetStringVal(loan_lenders, '/response/lenders/lender['||lenderIndex||']/lender_id/text()');
        --Sometimes the lender_id is null, but there is a value for either name or whereabouts.
        --We need to check name and whereabouts to differentiate a null because there are no more lenders, and a null
        --because the lender_id is missing.
        name := extractAndGetStringVal(loan_lenders, '/response/lenders/lender['||lenderIndex||']/name/text()');
        whereabouts := extractAndGetStringVal(loan_lenders, '/response/lenders/lender['||lenderIndex||']/whereabouts/text()');

        --Stop looping when we hit the first null lender_id, name, and whereabouts
        exit when lender_id is null and name is null and whereabouts is null;

        --Increment lenderOrder
        lenderOrder := lenderOrder + 1;

        --insert relationship into table
        insert into loan_lender(loan_id, lender_id, lenderOrder) values(loans.id, lender_id, lenderOrder);
      end loop; --all lenders on a page
    end loop; -- all pages for a loan

    --Commit the data every now and then.  This number should be increased as this function gets better.
    if mod(loans.id, 100) = 0 then
      commit; 
    end if;

  end loop; --all loans

  --Commit any remaining rows
  commit;
  
  --If there is an error, print the error information and the URL with the problem
  exception when others then
    dbms_output.put_line('Error with URL: '||url||chr(10)||dbms_utility.format_error_stack||chr(10)||
      dbms_utility.format_error_backtrace);
    raise;
end loadLoanLender;






----------------------------------------------------------------------------------------------------------------------
--Purpose: Fix some values that are not loaded correctly.
----------------------------------------------------------------------------------------------------------------------
procedure fixValues is
begin
  /* --OLD METHOD OF RE-LOADING SOME STATIC VALUES.

  --The partners.xml file has incorrect longitude, and doesn't include gdpPerCapitaPPP (I need a better place to
  --store that)
  --This was the query used to generate the SQL:
  --select 'update country set latitude = '||latitude||', longitude = '||longitude
  --  ||', gdpPerCapitaPPP = '||nvl(to_char(gdpPerCapitaPPP), 'null')
  --  ||' where name = q''!'||name||'!'';' sql
  --from kiva.country;
  
  
  update country set latitude = 13, longitude = 122, gdpPerCapitaPPP = 3300 where name = q'!Philippines!';
  update country set latitude = 10, longitude = -84, gdpPerCapitaPPP = 11600 where name = q'!Costa Rica!';
  update country set latitude = 46, longitude = 105, gdpPerCapitaPPP = 3200 where name = q'!Mongolia!';
  update country set latitude = 31.92157, longitude = 35.203285, gdpPerCapitaPPP = null where name = q'!Palestine!';
  update country set latitude = 16.166667, longitude = 107.833333, gdpPerCapitaPPP = 2800 where name = q'!Viet Nam!';
  update country set latitude = 13, longitude = -85, gdpPerCapitaPPP = 2900 where name = q'!Nicaragua!';
  update country set latitude = 33.833333, longitude = 35.833333, gdpPerCapitaPPP = 11100 where name = q'!Lebanon!';
  update country set latitude = -10, longitude = -76, gdpPerCapitaPPP = 8400 where name = q'!Peru!';
  update country set latitude = 15, longitude = -86.5, gdpPerCapitaPPP = 4400 where name = q'!Honduras!';
  update country set latitude = 15.5, longitude = -90.25, gdpPerCapitaPPP = 5200 where name = q'!Guatemala!';
  update country set latitude = -2, longitude = 30, gdpPerCapitaPPP = 900 where name = q'!Rwanda!';
  update country set latitude = 2, longitude = 33, gdpPerCapitaPPP = 1100 where name = q'!Uganda!';
  update country set latitude = -17, longitude = -65, gdpPerCapitaPPP = 4500 where name = q'!Bolivia!';
  update country set latitude = 8, longitude = 1.166667, gdpPerCapitaPPP = 911 where name = q'!Togo!';
  update country set latitude = 15, longitude = 30, gdpPerCapitaPPP = 2200 where name = q'!Sudan!';
  update country set latitude = 14, longitude = -14, gdpPerCapitaPPP = 1600 where name = q'!Senegal!';
  update country set latitude = 13, longitude = 105, gdpPerCapitaPPP = 2000 where name = q'!Cambodia!';
  update country set latitude = 15.623036831528264, longitude = 101.3818359375, gdpPerCapitaPPP = 8500 where name = q'!Thailand!';
  update country set latitude = 17, longitude = -4, gdpPerCapitaPPP = 1200 where name = q'!Mali!';
  update country set latitude = 9.5, longitude = 2.25, gdpPerCapitaPPP = 1500 where name = q'!Benin!';
  update country set latitude = 39, longitude = 71, gdpPerCapitaPPP = 2100 where name = q'!Tajikistan!';
  update country set latitude = -6, longitude = 35, gdpPerCapitaPPP = 1300 where name = q'!Tanzania!';
  update country set latitude = 13.833333, longitude = -88.916667, gdpPerCapitaPPP = 6200 where name = q'!El Salvador!';
  update country set latitude = 44.25, longitude = 17.833333, gdpPerCapitaPPP = 6500 where name = q'!Bosnia and Herzegovina!';
  update country set latitude = 1, longitude = 38, gdpPerCapitaPPP = 1600 where name = q'!Kenya!';
  update country set latitude = 8, longitude = -2, gdpPerCapitaPPP = 1500 where name = q'!Ghana!';
  update country set latitude = 28, longitude = 84, gdpPerCapitaPPP = 1100 where name = q'!Nepal!';
  update country set latitude = -5, longitude = 120, gdpPerCapitaPPP = 3900 where name = q'!Indonesia!';
  update country set latitude = -18.25, longitude = 35, gdpPerCapitaPPP = 900 where name = q'!Mozambique!';
  update country set latitude = 30, longitude = 70, gdpPerCapitaPPP = 2600 where name = q'!Pakistan!';
  update country set latitude = 19, longitude = -70.666667, gdpPerCapitaPPP = 9900 where name = q'!Dominican Republic!';
  update country set latitude = -22.993333, longitude = -57.996389, gdpPerCapitaPPP = 4200 where name = q'!Paraguay!';
  update country set latitude = 8.5, longitude = -11.5, gdpPerCapitaPPP = 700 where name = q'!Sierra Leone!';
  update country set latitude = 23, longitude = -102, gdpPerCapitaPPP = 14200 where name = q'!Mexico!';
  update country set latitude = 14.859850400601036, longitude = 19.16015625, gdpPerCapitaPPP = 1600 where name = q'!Chad!';
  update country set latitude = 40.5, longitude = 47.5, gdpPerCapitaPPP = 9000 where name = q'!Azerbaijan!';
  update country set latitude = 32.76880048488168, longitude = 43.59375, gdpPerCapitaPPP = 4000 where name = q'!Iraq!';
  update country set latitude = 8, longitude = -5, gdpPerCapitaPPP = 1700 where name = q'!Cote D'Ivoire!';
  update country set latitude = 10, longitude = 8, gdpPerCapitaPPP = 2300 where name = q'!Nigeria!';
  update country set latitude = -2.460181181020993, longitude = 24.609375, gdpPerCapitaPPP = 300 where name = q'!The Democratic Republic of the Congo!';
  update country set latitude = 18.95824648598139, longitude = -72.2021484375, gdpPerCapitaPPP = 1300 where name = q'!Haiti!';
  update country set latitude = 6, longitude = 12, gdpPerCapitaPPP = 2300 where name = q'!Cameroon!';
  update country set latitude = 33, longitude = 65, gdpPerCapitaPPP = 800 where name = q'!Afghanistan!';
  update country set latitude = 49, longitude = 32, gdpPerCapitaPPP = 6900 where name = q'!Ukraine!';
  update country set latitude = 47, longitude = 29, gdpPerCapitaPPP = 2500 where name = q'!Moldova!';
  update country set latitude = 43, longitude = 25, gdpPerCapitaPPP = 12900 where name = q'!Bulgaria!';
  update country set latitude = -13.583333, longitude = -172.333333, gdpPerCapitaPPP = 4900 where name = q'!Samoa!';
  update country set latitude = -2, longitude = -77.5, gdpPerCapitaPPP = 7500 where name = q'!Ecuador!';
  update country set latitude = 31.30906179986621, longitude = 34.30755615234375, gdpPerCapitaPPP = null where name = q'!Gaza!';
  update country set latitude = 39.76, longitude = -96.855469, gdpPerCapitaPPP = 47000 where name = q'!United States!';
  update country set latitude = 41, longitude = 74.619141, gdpPerCapitaPPP = 2100 where name = q'!Kyrgyzstan!';
  update country set latitude = 6.5, longitude = -9.316406, gdpPerCapitaPPP = 500 where name = q'!Liberia!';



  --Kiva doesn't store region longitude and latitude  
  --This was the query used to generate the SQL:
  --select 'update region set latitude = '||latitude||', longitude = '||longitude||' where name = '''||name||''';' sql
  --from kiva.region;
  
  update region set latitude = 11.86735091145932, longitude = -84.7265625 where name = 'Central America';
  update region set latitude = 28.459033019728043, longitude = -103.0078125 where name = 'North America';
  update region set latitude = 30.751277776257812, longitude = 45.703125 where name = 'Middle East';
  update region set latitude = -20.632784250388013, longitude = -54.84375 where name = 'South America';
  update region set latitude = 0, longitude = 25.3125 where name = 'Africa';
  update region set latitude = 47.279229002570815, longitude = 25.224609375 where name = 'Eastern Europe';
  update region set latitude = 18.312810846425442, longitude = 117.0703125 where name = 'Asia';


  --HTML characters should be stripped out of these values.
  --TODO: Fix this earlier in the load process.
  */


  update loan set country = q'!Cote D'Ivoire!' where country = 'Cote D&apos;Ivoire';
  --fix uneven apostrophe highlighting in some editors: '


  --South Sudan isn't a real country.
  update loan set country = 'Sudan' where country = 'South Sudan';


  update loan
  set activity = replace(activity, '&amp;', 'and')
  where activity like '%&amp;%';


  --This fails with ORA-600.
  --Refresh the metrics materialized view.  Takes about 10 seconds.
  --dbms_mview.refresh('MV_METRICS');


  commit;

end fixValues;









/*

------------------------------------------------------------------------------------------------------------------------
--Purpose: Write to exceptions table.  Use autonomous transaction so that data will always be commited.
------------------------------------------------------------------------------------------------------------------------
procedure logException(programName in varchar2, customData in varchar2, error_stack in varchar2,
  error_backtrace in varchar2, moreDataClob in clob default null, moreDataBlob in blob default null) as
  pragma autonomous_transaction;
begin
  insert into exceptions values(seq_exceptions.nextval, programName, customData, error_stack, error_backtrace
    ,sysdate, moreDataClob, moreDataBlob);
  commit;
end logException;



------------------------------------------------------------------------------------------------------------------------
--Purpose: Re-load all lender search information into the lenderSearch table.  Even though the API allows sorting,
--  the results are not always in the exact same order, so it's not really possible to use an incremental load.
--Inputs: maximumNumberOfPagesToLoad - Useful for debugging when only a small number of pages is needed for testing.
------------------------------------------------------------------------------------------------------------------------
procedure loadLenderSearch(maximumNumberOfPagesToLoad in number default null) is
  httpuri HttpUriType;
  lenders xmltype;
  lendersBlob blob;
  lendersClob clob;
  --Clobs suck for text manipulation, and it looks like all of the pages will be less than 32k, so let's use varchars.
  lendersText varchar2(32767);

  --Needed for blob to clob conversion
  destinationOffset number := 1;
  sourceOffset number := 1;
  blobCSID number := dbms_lob.default_csid;
  languageContext integer := dbms_lob.default_lang_ctx;
  warning integer;

  invalidCharacterPosition number;

  lastPageNumberLoaded number;
  numberOfPages number;
  lenderIndex number;

  lender_id varchar2(100);
  lender_name varchar2(100);
  lender_whereabouts varchar2(100);
  lender_country_code varchar2(100);
  lender_uid varchar2(100);

  xml_parsing_failed exception;
  pragma exception_init(xml_parsing_failed, -31011);
begin
  -------------------
  --Initialize
  -------------------
  --Remove the current data
  execute immediate 'truncate table lenderSearch';

  --Start with the last page (it's possible the last page wasn't full of data, so reload it)
  --select pageNumber into lastPageNumberLoaded from lastPageLoaded where dataloadtype = 'LENDERSEARCH';
  lastPageNumberLoaded := 1;

  --Load the first page.  Must sort by oldest if you want to ensure the same results for each page
  httpuri := HttpUriType('http://api.kivaws.org/v1/lenders/search.xml?sort_by=oldest&page='||to_char(lastPageNumberLoaded));
  lenders := httpuri.getxml();

  --Get the number of pages.
  numberOfPages := to_number(lenders.extract('/response/paging/pages/text()').getStringVal());

  --If a maximum number of pages was specified, only loop to that limit.
  if maximumNumberOfPagesToLoad is not null then
    numberOfPages := maximumNumberOfPagesToLoad;
  end if;

  --Loop through all the pages
  for pageIndex in lastPageNumberLoaded .. numberOfPages loop
    -------------------
    --Load Page
    -------------------
    --TODO: Replace with function
    begin
      --Even withouth trying to load incrementally we need a sort because the pages are always changing.
      httpuri := HttpUriType('http://api.kivaws.org/v1/lenders/search.xml?sort_by=oldest&page='||to_char(pageIndex));
      --lenders := httpuri.getxml();

      --Get blob, convert to clob.
      --Although httpuri.getxml() and httpuir.getclob would be much easier, each of them may throw an Oracle 600 error.
      --I think this has something to do with non-ascii characters after the 8 thousandth character.  The only safe way
      --to get the data is to first get a blob, convert to a clob, and then convert to xml.
      --(Note: Although a 600 error can be caught, but the session will still terminate, so there's no point.)
      dbms_lob.createtemporary(lendersBlob, true);
      dbms_lob.createtemporary(lendersClob, true);
      lendersblob := httpuri.getblob();
      --TODO: Is this character conversion correct?  The default worked during my testing, but I don't understand why.
      destinationOffset := 1; sourceOffset := 1; languageContext := dbms_lob.default_lang_ctx;
      dbms_lob.convertToClob(lendersclob, lendersblob, dbms_lob.lobmaxsize, destinationOffset, sourceOffset, 
        blobCSID, languageContext, warning);

      --Look for all instances of &#26; and write over it with spaces
      --Always start from the last occurance of the characters (these strings are long, we don't want to keep searching
      --the same part)
      invalidCharacterPosition := 1;
      loop
        --Look for pattern
        invalidCharacterPosition := dbms_lob.instr(lendersClob, '&#26;', invalidCharacterPosition);
        --If not found, then exit
        if invalidCharacterPosition = 0 then
          exit;
        --If found, write over the string.  CLOBs can't simply replace, we can only overwrite the values.
        else
          dbms_lob.write(lendersClob, 5, invalidCharacterPosition, '     ');
        end if;
      end loop;
      
      lenders := xmltype(lendersClob);

    --If there's an error loading the page, try to log some information
    exception when others then
      --Log some data about the page, including the entire contents of the page.
      --WARNING: Depending on the exception, the clob and blob logged may be for the previous page.
      logException('Lender Search Data Load', 'Page load error.  pageIndex = '||pageIndex
        ,dbms_utility.format_error_stack, dbms_utility.format_error_backtrace, lendersclob, lendersblob);

      --IMPOTANT: This time, don't re-raise.  I don't think there's any way to completely resolve this issue, so
      --there will just have to be some pages that aren't imported.
      raise;
    end;

    -------------------
    --Parse Results
    -------------------
    --Loop through each lender on the page
    lenderIndex := 0;
    loop
      begin
        lenderIndex := lenderIndex + 1;
        lender_id := extractAndGetStringVal(lenders, '/response/lenders/lender['||lenderIndex||']/lender_id/text()');
  
        --Stop looping when we hit the first null lender_id
        exit when lender_id is null;

        --Get the data
        lender_name := extractAndGetStringVal(lenders, '/response/lenders/lender['||lenderIndex||']/name/text()');
        lender_whereabouts := extractAndGetStringVal(lenders
          ,'/response/lenders/lender['||lenderIndex||']/whereabouts/text()');
        lender_country_code := extractAndGetStringVal(lenders
          ,'/response/lenders/lender['||lenderIndex||']/country_code/text()');
        lender_uid := extractAndGetStringVal(lenders, '/response/lenders/lender['||lenderIndex||']/uid/text()');
  
        --Insert the data
        insert into lenderSearch values(lender_id, lender_name, lender_whereabouts, lender_country_code, lender_uid);

      exception when others then 
        logException('Lender Search Data Load', 'Parse error.  pageIndex = '||pageIndex||', lenderIndex = '||lenderIndex
          ,dbms_utility.format_error_stack, dbms_utility.format_error_backtrace);
      end; --block for lender looping
    end loop; --end loop each lender

    --Commit the data every now and then, and write new high-water mark.
    --(this number should be increased as this function gets better)
    if mod(pageIndex, 10) = 0 or pageIndex = numberOfPages then
      --update lastPageLoaded set pageNumber = pageIndex where dataloadtype = 'LENDERSEARCH';
      commit; 
    end if;
  end loop; --end loop each page

end loadLenderSearch;




----------------------------------------------------------------------------------------------------------------------
--Purpose: Download and insert lender information.  This procedure will incrementally load - only lenders that are
--  in loan_lender but not in lender are included.  Lender_ids can change, so there's a chance a lender_id won't load.
----------------------------------------------------------------------------------------------------------------------
procedure loadLender is
  lendersXML xmltype;
  type lender_id_type is table of lender.lender_id%type;
  lender_ids lender_id_type;

  cursor lender_id_cursor is
  select distinct lender_id from loan_lender where lender_id not in (select lender_id from lender);
  --Use rownum for testing

  lender_id_csv varchar2(4000);

  lenderIndex number;
  v_lender_id varchar2(100);

  v_name varchar2(100);
  v_template_id number;
  v_image_id number;
  v_whereabouts varchar2(100);
  v_country_code varchar2(100);
  v_lender_uid varchar2(100);
  v_member_since date;
  v_personal_url varchar2(4000);
  v_occupation varchar2(4000);
  v_loan_because varchar2(4000);
  v_occupational_info varchar2(4000);
  v_loan_count number;
  v_invitee_count number;

  lenderCount number := 0;

begin
  --Open cursor
  open lender_id_cursor;

  --Loop through all of the lenders, get 50 at a time (that's the max allowed by the API)
  loop
    fetch lender_id_cursor bulk collect into lender_ids limit 50;

    --Exit when there are no lender_ids left to process
    exit when lender_ids.count = 0;

    --Create a comma-separated string of the lender_ids
    lender_id_csv := null;
    for i in 1 .. lender_ids.count loop
      lender_id_csv := lender_id_csv||','||lender_ids(i);
    end loop;
    lender_id_csv := substr(lender_id_csv, 2);

    --Retrieve lender information
    lendersXML := getXMLFromURL('http://api.kivaws.org/v1/lenders/'||lender_id_csv||'.xml');
    
    dbms_output.put_line('http://api.kivaws.org/v1/lenders/'||lender_id_csv||'.xml');

    --Loop through each lender on the page
    lenderIndex := 0;
    loop
      --Increment counter, look for next lender_id and exit when it is not found
      lenderIndex := lenderIndex + 1;
      v_lender_id := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/lender_id/text()');
      exit when v_lender_id is null;

      --Get values
      v_name := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/name/text()');
      v_template_id := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/image/template_id/text()');
      v_image_id := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/image/id/text()');
      v_whereabouts := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/whereabouts/text()');
      v_country_code := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/country_code/text()');
      v_lender_uid := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/uid/text()');
      --Remove the T and Z, convert to a date
      v_member_since := to_date(replace(replace(
        extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/member_since/text()')
        ,'T', ' '), 'Z', null), 'YYYY-MM-DD HH24:MI:SS');
      v_personal_url := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/personal_url/text()');
      v_occupation := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/occupation/text()');
      v_loan_because := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/loan_because/text()');
      v_occupational_info := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/occupational_info/text()');
      v_loan_count := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/loan_count/text()');
      v_invitee_count := extractAndGetStringVal(lendersXML, '/response/lenders/lender['||lenderIndex||']/invitee_count/text()');

      --Now insert the values into the table
      --TODO: Save in nested table and then bulk insert.  This should inrease performance, but this function is already
      --much faster than the other data load procedures
      insert into lender values
      (
        v_lender_id, v_name, v_template_id,  v_image_id, v_whereabouts, v_country_code, v_lender_uid, v_member_since
        ,v_personal_url, v_occupation, v_loan_because, v_occupational_info, v_loan_count, v_invitee_count
      );

      --Commit the data occasionally.  This number should be increased as the function gets better
      lenderCount := lenderCount + 1;
      if mod(lenderCount, 100) = 0 then
        commit; 
      end if;

    end loop; --end loop each lender

  end loop; --end loop through 50 lenders at a time

  --Commit any remaining rows
  commit;
end loadLender;
*/


end pkg_dataLoad;
/



show errors
